// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using Kailua.net.windward.utils;
using net.windward.utils.ado.ODBC;

namespace net.windward.utils.ado.MySql
{
	/// <summary>
	/// A MySql database.
	/// </summary>
	public class WrMySqlDatabase : IWrDatabase
	{
		private readonly DbProviderFactory provider;
		private readonly string server;
		private readonly string database;
		private static readonly Hashtable types;
		private readonly IWrSyntax syntax;

		private WrTable[] tables;
		private WrView[] views;
		private WrStoredProcedure[] procedures;

		static WrMySqlDatabase()
		{
			types = new Hashtable();
            // numeric
			types.Add("bigint", DbType.Int64);
			types.Add("tinyint", DbType.Byte);
			types.Add("smallint", DbType.Int16);
			types.Add("mediumint", DbType.Int32);
			types.Add("int", DbType.Int32);
            types.Add("integer", DbType.Int32);
            types.Add("float", DbType.Single);
            types.Add("double", DbType.Double);
            types.Add("decimal", DbType.Decimal);
            types.Add("numeric", DbType.Decimal);
            types.Add("bool", DbType.Byte);
            types.Add("boolean", DbType.Byte);
            // date & time
			types.Add("datetime", DbType.DateTime);
			types.Add("date", DbType.DateTime);
			types.Add("timestamp", DbType.DateTime);
			types.Add("time", DbType.Object);
			types.Add("year", DbType.Int32);
            // string
			types.Add("char", DbType.String);
			types.Add("varchar", DbType.String);
			types.Add("binary", DbType.String);
			types.Add("varbinary", DbType.String);
			types.Add("blob", DbType.Binary);
            types.Add("tinyblob", DbType.Binary);
            types.Add("mediumblob", DbType.Binary);
            types.Add("longblob", DbType.Binary);
			types.Add("text", DbType.String);
            types.Add("tinytext", DbType.String);
            types.Add("mediumtext", DbType.String);
            types.Add("longtext", DbType.String);
            // other/unsupported
            types.Add("bit", DbType.Binary); // bugbug: use DbType.Double instead?  DbType.String? according to the mysql reference, bit returns binary data
            types.Add("enum", DbType.Object);
            types.Add("set", DbType.Object);
		}

		/// <summary>
		/// Create a MySql database object.
		/// </summary>
		/// <param name="provider">The provider for this server.</param>
		/// <param name="server">The name of the server the database is on.</param>
		/// <param name="database">The name of the database.</param>
		public WrMySqlDatabase(DbProviderFactory provider, string server, string database)
		{
			this.provider = provider;
			this.server = server;
			this.database = database;
			syntax = new WrMySqlSyntax(provider);
		}

		/// <summary>
		/// The name of the database.
		/// </summary>
		public string Name
		{
			get { return database; }
		}

		/// <summary>
		/// The default schema for all objects in this database. Returns null if there is no default.
		/// </summary>
		public string DefaultSchema
		{
			get
			{
				return null;
			}
		}

		/// <summary>
		/// All tables in this database. Must call LoadMetadata first.
		/// </summary>
		public WrTable[] Tables
		{
			get { return tables; }
		}

		/// <summary>
		/// All views in this database. Must call LoadMetadata first.
		/// </summary>
		public WrView[] Views
		{
			get { return views; }
		}

		/// <summary>
		/// Return all stored procedures in this database. Must call LoadMetadata first.
		/// </summary>
		public WrStoredProcedure[] StoredProcedures
		{
			get { return procedures; }
		}

		/// <summary>
		/// Loads the metadata for this database.
		/// </summary>
		/// <param name="credentials">The user credentials to access the database.</param>
		/// <param name="owner">Determines what metadata objects are read and if full metadata is retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		public void LoadMetadata(WrCredentials credentials, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{
			LoadMetadata(ConnectionString("INFORMATION_SCHEMA", credentials), owner, status);
		}

		/// <summary>
		/// Loads the metadata for this database.
		/// </summary>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what metadata objects are read and if full metadata is retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		public void LoadMetadata(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{
			if (status != null)
				status(WrDatabase.TYPE.START, database);

			tables = GetTables(connStr, owner, status);
			for (int index = 0; index < tables.Length; index++)
			{
				WrColumn[] columns = GetColumns(tables[index], connStr, owner, status);
				tables[index] = new WrTable(tables[index].Schema, tables[index].Name, tables[index].Description, tables[index].IsSystem, columns);
			}

			views = GetViews(connStr, owner, status);
			for (int index = 0; index < views.Length; index++)
			{
				WrColumn[] columns = GetColumns(views[index], connStr, owner, status);
				views[index] = new WrView(views[index].Schema, views[index].Name, views[index].Description, views[index].IsSystem, columns);
			}

			procedures = GetStoredProcedures(connStr, owner, status);
		}

		/// <summary>
		/// Return all tables in this database.
		/// </summary>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what tables are returned and if metadata is also retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		/// <returns>all tables in this database.</returns>
		private WrTable[] GetTables(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{

			if ((owner & WrVendor.ELEM_OWNER.USER) == 0)
				return new WrTable[0];
			try
			{
				using (DbConnection conn = provider.CreateConnection())
				{
					conn.ConnectionString = connStr;
					conn.Open();
					using (DbCommand cmd = conn.CreateCommand())
					{
						cmd.CommandType = CommandType.Text;
                        if ((owner & WrVendor.ELEM_OWNER.DESCRIPTIONS) != 0)
                            cmd.CommandText = "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and table_schema = ?database order by TABLE_NAME";
                        else
                            cmd.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and table_schema = ?database order by TABLE_NAME";
                        cmd.Parameters.Add(MakeParameter("?database", database));
                        ArrayList rtn = new ArrayList();
                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            bool hasDesc = reader.FieldCount > 1;
                            while (reader.Read())
                            {
                                string description = "";
                                if (hasDesc)
                                    description = reader.GetString(1);
                            	string name = reader.GetString(0);
                                rtn.Add(new WrTable(null, name, description, false, null));

								if (status != null)
									status(WrDatabase.TYPE.TABLE, name);
                            }
                        }

						rtn.Sort();
                        return (WrTable[])rtn.ToArray(typeof(WrTable));
					}
				}
			}
			catch (Exception)
			{
				return new WrTable[0];
			}
		}

		/// <summary>
		/// Return all views in this database.
		/// </summary>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what views are returned and if metadata is also retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		/// <returns>all views in this database.</returns>
		private WrView[] GetViews(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{

			if ((owner & WrVendor.ELEM_OWNER.USER) == 0)
				return new WrView[0];

			try
			{
				using (DbConnection conn = provider.CreateConnection())
				{
					conn.ConnectionString = connStr;
					conn.Open();
					using (DbCommand cmd = conn.CreateCommand())
					{
						cmd.CommandType = CommandType.Text;
                        if ((owner & WrVendor.ELEM_OWNER.DESCRIPTIONS) != 0)
                            cmd.CommandText = "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'VIEW' and table_schema = ?db order by TABLE_NAME";
                        else
                            cmd.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'VIEW' and table_schema = ?db order by TABLE_NAME";
						cmd.Parameters.Add(MakeParameter("?db", database));
						ArrayList rtn = new ArrayList();
						using (IDataReader reader = cmd.ExecuteReader())
						{
                            bool hasDesc = reader.FieldCount > 1;
                            while (reader.Read())
                            {
                                string description = "";
                                if (hasDesc)
                                    description = reader.GetString(1);
                            	string name = reader.GetString(0);
                                rtn.Add(new WrView(null, name, description, false, null));

								if (status != null)
									status(WrDatabase.TYPE.VIEW, name);
							}
						}
						rtn.Sort();
						return (WrView[])rtn.ToArray(typeof(WrView));
					}
				}
			}
			catch (Exception)
			{
				return new WrView[0];
			}
		}

		/// <summary>
		/// Return all columns in a table or view.
		/// </summary>
		/// <param name="table">The name of the table or view.</param>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what columns are returned and if metadata is also retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		/// <returns>all columns in a table or view.</returns>
		private WrColumn[] GetColumns(WrBaseTable table, string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{
            try
			{
				using (DbConnection conn = provider.CreateConnection())
				{
					conn.ConnectionString = connStr;
					conn.Open();
					using (DbCommand cmd = conn.CreateCommand())
					{
						cmd.CommandType = CommandType.Text;
                        if ((owner & WrVendor.ELEM_OWNER.DESCRIPTIONS) != 0)
                            cmd.CommandText = "SELECT COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS where table_schema = ?db and table_name = ?table";
                        else
                            cmd.CommandText = "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS where table_schema = ?db and table_name = ?table";
                        cmd.Parameters.Add(MakeParameter("?db", database));
                        cmd.Parameters.Add(MakeParameter("?table", table.FullName));

						ArrayList rtn = new ArrayList();
						using (IDataReader reader = cmd.ExecuteReader())
						{
                            bool hasDesc = reader.FieldCount > 2;
							while (reader.Read())
							{
								string str = reader.GetString(1).ToLower();
                                if (str.IndexOf('(') != -1)
                                    str = str.Substring(0, str.IndexOf('('));
                                DbType typ = (DbType)(types.Contains(str) ? types[str] : DbType.Object);

                                string description = "";
                                if (hasDesc)
                                    description = reader.GetString(2);
								string name = reader.GetString(0);
								rtn.Add(new WrColumn(name, description, typ));

								if (status != null)
									status(table is WrTable ? WrDatabase.TYPE.TABLE_COLUMN : WrDatabase.TYPE.VIEW_COLUMN, table.FullName + '.' + name);
							}
						}

                        if ((owner & WrVendor.ELEM_OWNER.META_DATA) != 0)
                        {
                            // set the pk-fk mappings
                            try
                            {
                                FkPkMappings(conn, rtn, table.FullName);
                            }
                            catch (Exception)
                            {
                                // nada
                            	Trap.trap();
                            }
                        }

						return (WrColumn[]) rtn.ToArray(typeof (WrColumn));
					}
				}
			}
			catch (Exception)
			{
				return new WrColumn[0];
			}
		}

        /// <summary>
        /// Sets the PrimaryKey of each foreign-key in the specified columns (WrColumn) of the specified table.
        /// </summary>
        /// <param name="conn">The connection to the database containing the specified table.</param>
        /// <param name="columns">The columns.  These must be of type WrColumn</param>
        /// <param name="table">The table's name.</param>
        private void FkPkMappings(DbConnection conn, IList columns, string table)
        {
            // identify the primary key's table and column for each constrained WrColumn in columns
            for (int ind = 0; ind < columns.Count; ind++)
            {
                WrColumn colOn = (WrColumn)columns[ind];

                // get the constraint - if one exists
                using (DbCommand cmd = provider.CreateCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText =
                        "SELECT column_name, position_in_unique_constraint, referenced_table_name, referenced_column_name, table_name, table_schema " +
                        "FROM information_schema.key_column_usage " +
                        "WHERE (column_name = ?col) AND (table_name = ?table) AND (table_schema = ?db) AND (position_in_unique_constraint IS NOT null)";
                    cmd.Parameters.Add(MakeParameter("?col", colOn.FullName));
                    cmd.Parameters.Add(MakeParameter("?table", table));
                    cmd.Parameters.Add(MakeParameter("?db", database));

                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        if (!reader.Read())
                            continue;

                        string pkTable = reader.GetString(2);
                        string pkColumn = reader.GetString(3);
                        columns[ind] = new WrColumn(colOn.FullName, colOn.Description, colOn.DbType, pkTable + "." + pkColumn);
                    }
                }
            }
        }

		/// <summary>
		/// Return all stored procedures in this database.
		/// </summary>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what stored procedures are returned and if metadata is also retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		/// <returns>all stored procedures in this database.</returns>
		private WrStoredProcedure[] GetStoredProcedures(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{

			if ((owner & WrVendor.ELEM_OWNER.USER) == 0)
				return new WrStoredProcedure[0];

			try
			{
				using (DbConnection conn = provider.CreateConnection())
				{
					conn.ConnectionString = connStr;
					conn.Open();
					List<ProcName> procNames = new List<ProcName>();
					using (DbCommand cmd = conn.CreateCommand())
					{
						cmd.CommandType = CommandType.Text;
						if ((owner & WrVendor.ELEM_OWNER.DESCRIPTIONS) != 0)
							cmd.CommandText =
								"SELECT ROUTINE_NAME, ROUTINE_COMMENT FROM INFORMATION_SCHEMA.ROUTINES where (ROUTINE_TYPE = 'PROCEDURE') and (ROUTINE_SCHEMA = ?db) order by ROUTINE_NAME";
						else
							cmd.CommandText =
								"SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES where (ROUTINE_TYPE = 'PROCEDURE') and (ROUTINE_SCHEMA = ?db) order by ROUTINE_NAME";
						cmd.Parameters.Add(MakeParameter("?db", database));
						using (IDataReader reader = cmd.ExecuteReader())
						{
							bool hasDesc = reader.FieldCount > 1;
							while (reader.Read())
							{
								string name = reader.GetString(0);
								if (status != null)
									status(WrDatabase.TYPE.PROCEDURE, name);
								string description = hasDesc ? reader.GetString(1) : "";
								procNames.Add(new ProcName(name, description));
							}
						}
					}

					// and now the parameters
					List<WrStoredProcedure> procs = new List<WrStoredProcedure>();
					foreach (ProcName procOn in procNames)
					{
						List<WrProcParam> procParams = new List<WrProcParam>();
						using (DbCommand cmd = conn.CreateCommand())
						{
							cmd.CommandType = CommandType.Text;
							cmd.CommandText = string.Format("SHOW CREATE PROCEDURE {0}", procOn.name);
							using (IDataReader reader = cmd.ExecuteReader())
							{
								if (reader.Read())
								{
									// CREATE DEFINER=`root`@`localhost` PROCEDURE `rewards_report`(
									//     IN min_monthly_purchases TINYINT UNSIGNED
									//    , IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED
									//    , OUT count_rewardees INT
									// )
									// *** we have to skip the () in (10,2) and change the , to a ;
									string func = reader.GetString(2);
									StringBuilder buf = new StringBuilder(func.Substring(func.IndexOf('(') + 1));
									int inParen = 0;
									for (int ind = 0; ind < buf.Length; ind++)
									{
										if (buf[ind] == '(')
										{
											inParen++;
											continue;
										}
										if (buf[ind] == ')')
										{
											inParen--;
											if (inParen < 0)
											{
												buf.Remove(ind, buf.Length - ind);
												break;
											}
											continue;
										}
										if ((buf[ind] == ',') && (inParen > 0))
											buf[ind] = ';';
									}
									func = buf.ToString().Trim();

									string[] parms = func.Split(new char[] {','}, StringSplitOptions.RemoveEmptyEntries);
									foreach (string paramOn in parms)
									{
										string[] items = paramOn.Split(new char[] {' '}, StringSplitOptions.RemoveEmptyEntries);
										if (items.Length < 3)
											continue;
										ParameterDirection dir;
										switch (GetItem(items, 0).ToUpper())
										{
											case "IN":
												dir = ParameterDirection.Input;
												break;
											case "INOUT":
												dir = ParameterDirection.InputOutput;
												break;
											case "OUT":
												dir = ParameterDirection.Output;
												break;
											default:
												Trap.trap();
												dir = ParameterDirection.InputOutput;
												break;
										}
										string typ = GetItem(items, 2);
										if (typ.IndexOf('(') != -1)
											typ = typ.Substring(0, typ.IndexOf('(')).Trim();
										procParams.Add(new WrProcParam(GetItem(items, 1), WrOdbcDatabase.ConvertFromRawType(typ), 0,
										                               false, dir));
									}
								}
							}
						}
						procs.Add(new WrStoredProcedure(null, procOn.name, procOn.description, false, procParams.ToArray()));
					}
					return procs.ToArray();
				}
			}
			catch (Exception)
			{
				return new WrStoredProcedure[0];
			}
		}

		private static string GetItem(string[] items, int index)
		{
			return items[index].Trim();
		}

		private class ProcName
		{
			public readonly string name;
			internal readonly string description;
			public ProcName(string name, string description)
			{
				this.name = name;
				this.description = description;
			}
		}

		private string ConnectionString(string dbName, WrCredentials credentials)
		{
			return WrMySqlVendor.ConnectionString(provider, server, database, credentials, true);
		}

		private void SetParameter(DbCommand cmd, object value)
		{
			if (value == null)
				value = DBNull.Value;

			DbParameter param = provider.CreateParameter();
			param.ParameterName = "?";
			param.Value = value;

			cmd.Parameters.Add(param);
		}

        private DbParameter MakeParameter(string name, object value)
        {
            if (value == null)
                value = DBNull.Value;

            DbParameter param = provider.CreateParameter();
            param.ParameterName = name;
            param.Value = value;

            return param;
        }
	}
}
